import os
import sys
import time
import math
import struct
import csv
import psycopg2
import psycopg2.extras
from osgeo import gdal

start = time.time()
last_step = time.time()

connection = psycopg2.connect("dbname='beaver' user='postgres' host='localhost'");
sql = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)


gdal.SetCacheMax(54)
dem_file = sys.argv[1].split('=')[1]
# '/Volumes/Wren/Users/spring/projects/beaver/data/elevation/uw/tiff/methow-tiled.tif'

def get_time(time):
    if time/60 < 1:
      seconds = '%s seconds' % str(time)
      return seconds
    else:
      minutes = '%s minutes' % str(time/60)
      return minutes

def elapsed():
    total = (time.time() - start)
    last = (time.time() - last_step)
    return 'Total time: %s | Last step: %s' % (get_time(total), get_time(last))

def color_print(color,text):
    print
    print "\033[9%sm%s\033[0m" % (color,text)
    print

dataset = gdal.Open(dem_file, gdal.GA_ReadOnly)
geotransform = dataset.GetGeoTransform() 
min_x = geotransform[0]
max_y = geotransform[3]
res_x = abs(geotransform[1])
res_y = abs(geotransform[5])

sql.execute("""SELECT oid, ST_X(ST_StartPoint(geometry)) as down_x, ST_Y(ST_StartPoint(geometry)) as down_y,ST_X(ST_EndPoint(geometry)) as up_x, ST_Y(ST_EndPoint(geometry)) as up_y FROM sshiap_splits ORDER by OID;""")
color_print(3,elapsed())

query_records = float(sql.rowcount) #- 1.0

print 'beginning lookup of %s features...' % query_records
print

sql_list = []
sql_insert = open('scripts/elevation_insert.sql', 'wb')
sql_insert.write("set client_min_messages = 'warning';\n")
sql_insert.write('DROP TABLE IF EXISTS elevation;\n')
sql_insert.write('CREATE TABLE elevation (oid int, lowpoint int, highpoint int);\n')

for row in sql.fetchall():
    oid = row['oid']
    down_x = row['down_x']
    down_y = row['down_y']
    up_x = row['up_x']
    up_y = row['up_y']
    down_row = int(math.ceil((max_y - down_y)/res_y))
    down_col = int(math.ceil((down_x - min_x)/res_x))
    up_row = int(math.ceil((max_y - up_y)/res_y))
    up_col = int(math.ceil((up_x - min_x)/res_x))
    if down_row > 0  and down_col > 0 and up_row > 0  and up_col > 0 and dataset.RasterYSize > down_row and dataset.RasterXSize > down_col and dataset.RasterYSize > up_row and dataset.RasterXSize > up_col:
        down_raster_scan = dataset.ReadRaster(down_col - 1, down_row - 1, 1, 1)
        down_raster_area = struct.unpack('f' * 1, down_raster_scan)
        lowpoint = int(down_raster_area[0])
        up_raster_scan = dataset.ReadRaster(up_col - 1, up_row - 1, 1, 1)
        up_raster_area = struct.unpack('f' * 1, up_raster_scan)
        highpoint = int(up_raster_area[0])
        sql_insert.write('INSERT INTO elevation VALUES (%s, %s, %s);\n' % (oid, lowpoint, highpoint))
        if oid/query_records in [.1,.2,.5,.10,.15,.20, .25,.30,.35,.40,.45,.50,.55,.60,.65,.70,.75,.80,.85,.90,.95,1.0]:
          print '%s%s completed' % (oid/query_records*100, '%')
    else:
        print 'error ---------------------------------------------------------------------------------'

connection.commit()
sql_insert.close()
color_print(3,elapsed())
